package com.swak.frame.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.springframework.web.multipart.MultipartFile;

import com.swak.frame.logger.SwakLoggerFactory;
import com.swak.frame.util.DateTimeUtils;

public class POIExcelHelper {
    private final Logger log = SwakLoggerFactory.getLogger(this.getClass());

    /*** excel文件输入流 */
    private InputStream is;

    /*** 工作薄 */
    private Workbook workbook;

    /** sheet */
    private Sheet sheet;

    private static final String DATEFORMAT = "yyyy-MM-dd";

    /**
     * 日期格式化
     */
    public static final SimpleDateFormat SDF1 = new SimpleDateFormat(DATEFORMAT);

    public static final int MAX_ROW = 65500;

    public Workbook getWorkbook() {
        return workbook;
    }

    /**
     * 使用文件构造
     * 
     * @param excelFile
     */
    public boolean openExcel(File excelFile) {
        boolean rtn = true;
        // 判断文件是否为空
        if (excelFile == null) {
            log.error("ExcelImpl.openExcel,the File is null");
            rtn = false;
        }
        if (!excelFile.exists()) {
            log.error("ExcelImpl.openExcel,the File not exist");
            rtn = false;
        }
        // 判断是否以xls或xlsx开头
        String fileName = excelFile.getName();
        if (!(fileName.endsWith(".xls") || fileName.endsWith("xlsx"))) {
            log.error("ExcelImpl.openExcel,the File is not Excel File!");
            rtn = false;
        }
        boolean xlsx = false;
        if (fileName.endsWith(".xlsx")) {
            xlsx = true;
        }
        if (rtn) {
            try {
                is = new FileInputStream(excelFile);
                // 构建workbook
                if (xlsx) {
                    workbook = new XSSFWorkbook(is);
                } else {
                    workbook = new HSSFWorkbook(is);
                }
            } catch (Exception e) {
                log.error("POIExcel.openExcel error,the errorMessage is =" + e.getMessage());
                rtn = false;
            }
        }
        return rtn;
    }


    /**
     * 使用InputStream构造
     * 
     * @param InputStream isXlsx
     */
    public boolean openExcel(InputStream input, boolean isXlsx) {
        // 判断文件是否为空
        if (input == null) {
            log.error("ExcelImpl.openExcel,the File is null");
            return false;
        }
        try {
            // 构建workbook
            if (isXlsx) {
                workbook = new XSSFWorkbook(input);
            } else {
                workbook = new HSSFWorkbook(input);
            }
        } catch (Exception e) {
            log.error("POIExcel.openExcel error,the errorMessage is =" + e.getMessage());
            return false;
        }
        return true;
    }


    /**
     * 使用MultipartFile构造
     * 
     * @param MultipartFile
     */
    public boolean openExcel(MultipartFile file) {
        // 判断文件是否为空
        if (file == null) {
            log.error("ExcelImpl.openExcel,the File is null");
            return false;
        }
        // 判断是否以xls或xlsx开头
        String fileName = file.getOriginalFilename();
        if (!(fileName.endsWith(".xls") || fileName.endsWith("xlsx"))) {
            log.error("ExcelImpl.openExcel,the File is not Excel File!");
            return false;
        }
        boolean isXlsx = false;
        if (fileName.endsWith(".xlsx")) {
            isXlsx = true;
        }
        try {
            return openExcel(file.getInputStream(), isXlsx);
        } catch (Exception e) {
            log.error("POIExcel.openExcel error,the errorMessage is =" + e.getMessage());
            return false;
        }
    }

    public int getNumberOfSheets() {
        return workbook.getNumberOfSheets();
    }

    public void close() {
        try {
            // 关闭输入流
            if (is != null) {
                is.close();
                is = null;
            }
            // 关闭工作薄
            if (workbook != null) {
                workbook = null;
            }
            System.gc();
        } catch (Exception e) {
            System.gc();
            log.error("POIExcel.openExcel error,the errorMessage is =" + e.getMessage());
        }
    }

    public boolean isExistSheet(String sheetName) {
        Sheet sheet = this.workbook.getSheet(sheetName);
        if (sheet == null) {
            return false;
        } else {
            return true;
        }
    }

    public String getAsString(String sheetName, int row, int column) {
        // 获取工作指定的cell
        Cell cell = getCell(sheetName, row, column);
        if (cell == null) {
            return null;
        }
        // 判断类型
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
            && HSSFDateUtil.isCellDateFormatted(cell)) {
            Date temp = new Date(cell.getDateCellValue().getTime());
            return DateTimeUtils.date2String(temp, DATEFORMAT);
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
            return String.valueOf(cell.getErrorCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            return "";
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        } else {
            return DateTimeUtils.date2String(cell.getDateCellValue(), DATEFORMAT);
        }
    }


    public String getAsString(int sheetIndex, int row, int column) {
        // 获取工作指定的cell
        Cell cell = getCell(sheetIndex, row, column);
        String cellValue = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        // 如果是date类型则 ，获取该cell的date值
                        cellValue = DateTimeUtils.date2String(
                            HSSFDateUtil.getJavaDate(cell.getNumericCellValue()), DATEFORMAT);
                    } else { // 纯数字
                        cellValue = getNumberAsString(cell);
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING: // 字符串
                    cellValue = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                    cellValue = cell.getCellFormula();
                    break;
                case HSSFCell.CELL_TYPE_BLANK: // 空值
                    cellValue = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR: // 故障
                    cellValue = String.valueOf(cell.getErrorCellValue());
                    break;
                default:
                    cellValue = "";
                    break;
            }
        }
        return cellValue;
        /*
         * // 判断类型
         * if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
         * return cell.getStringCellValue();
         * } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
         * return String.valueOf(cell.getBooleanCellValue());
         * } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
         * && HSSFDateUtil.isCellDateFormatted(cell)) {
         * Date temp = new Date(cell.getDateCellValue().getTime());
         * return SDF.format(temp);
         * } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
         * return getNumberAsString(cell);
         * } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
         * return String.valueOf(cell.getErrorCellValue());
         * } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
         * return "";
         * } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
         * return cell.getCellFormula();
         * } else {
         * return SDF.format(cell.getDateCellValue());
         * }
         */ }

    public String getNumAsString(int sheetIndex, int row, int column) {
        // 获取工作指定的cell
        Cell cell = getCell(sheetIndex, row, column);
        if (cell == null) {
            return null;
        }
        cell.setCellType(Cell.CELL_TYPE_STRING);
        return cell.getRichStringCellValue().toString();
    }

    public String getNumAsString(String sheetName, int row, int column) {
        // 获取工作指定的cell
        Cell cell = getCell(sheetName, row, column);
        if (cell == null) {
            return null;
        }
        cell.setCellType(Cell.CELL_TYPE_STRING);
        return cell.getRichStringCellValue().toString();
    }


    public Date getAsDate(String sheetName, int row, int column) {
        // 获取工作指定的cell
        Cell cell = getCell(sheetName, row, column);
        // 尝试以日期形式获取
        try {
            return cell.getDateCellValue();
        } catch (Exception e) {
            // 说明是字符串格式，而不是日期格式
            String str = cell.getStringCellValue();
            try {
                // 空字符串则返回null
                if (StringUtils.isEmpty(str)) {
                    return null;
                } else {
                    return DateUtils.parseDate(str, new String[] {DATEFORMAT});
                }
            } catch (ParseException e1) {
                log.error("POIExcel.getAsDate error,the errorMessage is =" + e.getMessage());
            }
        }
        return null;
    }

    public Date getAsDate(int sheetIndex, int row, int column) {
        // 获取工作指定的cell
        Cell cell = getCell(sheetIndex, row, column);
        // 尝试以日期形式获取
        try {
            return cell.getDateCellValue();
        } catch (Exception e) {
            // 说明是字符串格式，而不是日期格式
            String str = cell.getStringCellValue();
            try {
                // 空字符串则返回null
                if (StringUtils.isEmpty(str)) {
                    return null;
                } else {
                    return DateUtils.parseDate(str, new String[] {DATEFORMAT});
                }
            } catch (ParseException e1) {
                log.error("POIExcel.getAsDate error,the errorMessage is =" + e.getMessage());
            }
        }
        return null;
    }

    public double getAsNumber(String sheetName, int row, int column) {
        // 获取工作指定的cell
        Cell cell = getCell(sheetName, row, column);
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return cell.getNumericCellValue();
        }
        return 0;
    }

    public double getAsNumber(int sheetIndex, int row, int column) {
        // 获取工作指定的cell
        Cell cell = getCell(sheetIndex, row, column);
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return cell.getNumericCellValue();
        }
        return 0;
    }

    public String getNumberAsString(String sheetName, int row, int column) {
        // 获取工作指定的cell
        float temp = 0;
        Cell cell = getCell(sheetName, row, column);
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            try {
                temp = Float.valueOf(
                    cell.getStringCellValue().substring(0, cell.getStringCellValue().length() - 1));
                DecimalFormat df = new DecimalFormat();
                df.applyPattern("##0.00");
                return df.format(temp) + "%";
            } catch (Exception e) {
                return null;
            }
        }
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            temp = (float) (cell.getNumericCellValue() * 100);
            DecimalFormat df = new DecimalFormat();
            df.applyPattern("##0.00");
            return df.format(temp) + "%";
        }
        return null;
    }

    public String getNumberAsString(Cell cell) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        return cell.getRichStringCellValue().toString();
        // return str;

        // return null;
    }

    public String getNumberAsString(int sheetIndex, int row, int column) {
        // 获取工作指定的cell
        float temp = 0;
        Cell cell = getCell(sheetIndex, row, column);
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            try {
                temp = Float.valueOf(
                    cell.getStringCellValue().substring(0, cell.getStringCellValue().length() - 1));
                DecimalFormat df = new DecimalFormat();
                df.applyPattern("##0.00");
                return df.format(temp) + "%";
            } catch (Exception e) {
                return null;
            }
        }
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            temp = (float) (cell.getNumericCellValue() * 100);
            DecimalFormat df = new DecimalFormat();
            df.applyPattern("##0.00");
            return df.format(temp) + "%";
        }

        return null;
    }

    public boolean has(String sheetName) {
        return workbook.getSheet(sheetName) != null;
    }

    public boolean has(int index) {
        return workbook.getSheetAt(index) != null;
    }

    public String getSheetNameAt(int index) {
        Sheet sheet = workbook.getSheetAt(index);
        if (sheet == null) {
            return null;
        }
        return sheet.getSheetName();
    }


    public int rowCount(String sheetName) {
        if (!has(sheetName)) {// 如果不存在
            return -1;
        }
        return workbook.getSheet(sheetName).getPhysicalNumberOfRows();
    }

    public int rowCount(int sheetIndex) {
        if (!has(sheetIndex)) {// 如果不存在
            return -1;
        }
        int rowCount = 1;
        int rows = workbook.getSheetAt(sheetIndex).getPhysicalNumberOfRows();
        for (int row = 1; row < rows; row++) {
            String cellVal = getAsString(sheetIndex, row, 0);
            if (StringUtils.isNotEmpty(cellVal)) {
                rowCount = rowCount + 1;
            }
        }
        return rowCount;
    }

    public int colCount(String sheetName, int rowIndex) {
        if (!has(sheetName)) {// 如果不存在
            return -1;
        }
        Row row = workbook.getSheet(sheetName).getRow(rowIndex);
        return row == null ? -1 : row.getPhysicalNumberOfCells();
    }

    public int colCount(int sheetIndex, int rowIndex) {
        if (!has(sheetIndex)) {// 如果不存在
            return -1;
        }
        Row row = workbook.getSheetAt(sheetIndex).getRow(rowIndex);
        if (row == null) {
            return -1;
        }
        int phyColCount = row.getPhysicalNumberOfCells();
        return phyColCount > 0 ? row.getLastCellNum() : -1;
    }

    /**
     * 获取指定工作表指定位置的cell
     * 
     * @param sheetName
     * @param row
     * @param column
     * @return
     */
    private Cell getCell(String sheetName, int row, int column) {
        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            return null;
        }
        Row tempRow = sheet.getRow(row);
        if (tempRow == null) {
            return null;
        } else {
            return workbook.getSheet(sheetName).getRow(row).getCell(column);
        }
    }

    private Cell getCell(int sheetIndex, int row, int column) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        if (sheet == null) {
            return null;
        }
        Row tempRow = sheet.getRow(row);
        if (tempRow == null) {
            return null;
        } else {
            return workbook.getSheetAt(sheetIndex).getRow(row).getCell(column);
        }
    }

    public int getFirstColumnOfMerged(String sheetName, int index) {
        // 获取合并的数量
        int mergedCount = getMergedCount(sheetName);
        // 校验
        if (mergedCount < index) {
            return -1;
        }
        // 获取合并单元格
        CellRangeAddress merged = workbook.getSheet(sheetName).getMergedRegion(index);
        if (merged == null) {
            return -1;
        }
        return merged.getFirstColumn();
    }

    public int getFirstColumnOfMerged(int sheetIndex, int index) {
        // 获取合并的数量
        int mergedCount = getMergedCount(sheetIndex);
        // 校验
        if (mergedCount < index) {
            return -1;
        }
        // 获取合并单元格
        CellRangeAddress merged = workbook.getSheetAt(sheetIndex).getMergedRegion(index);
        if (merged == null) {
            return -1;
        }
        return merged.getFirstColumn();
    }

    public int getFirstRowOfMerged(String sheetName, int index) {
        // 获取合并的数量
        int mergedCount = getMergedCount(sheetName);
        // 校验
        if (mergedCount < index) {
            return -1;
        }
        // 获取合并单元格
        CellRangeAddress merged = workbook.getSheet(sheetName).getMergedRegion(index);
        if (merged == null) {
            return -1;
        }
        return merged.getFirstRow();
    }

    public int getFirstRowOfMerged(int sheetIndex, int index) {
        // 获取合并的数量
        int mergedCount = getMergedCount(sheetIndex);
        // 校验
        if (mergedCount < index) {
            return -1;
        }
        // 获取合并单元格
        CellRangeAddress merged = workbook.getSheetAt(sheetIndex).getMergedRegion(index);
        if (merged == null) {
            return -1;
        }
        return merged.getFirstRow();
    }

    public int getLastColumnOfMerged(String sheetName, int index) {
        // 获取合并的数量
        int mergedCount = getMergedCount(sheetName);
        // 校验
        if (mergedCount < index) {
            return -1;
        }
        // 获取合并单元格
        CellRangeAddress merged = workbook.getSheet(sheetName).getMergedRegion(index);
        if (merged == null) {
            return -1;
        }
        return merged.getLastColumn();
    }

    public int getLastRowOfMerged(String sheetName, int index) {
        // 获取合并的数量
        int mergedCount = getMergedCount(sheetName);
        // 校验
        if (mergedCount < index) {
            return -1;
        }
        // 获取合并单元格
        CellRangeAddress merged = workbook.getSheet(sheetName).getMergedRegion(index);
        if (merged == null) {
            return -1;
        }
        return merged.getLastRow();
    }

    public String getMergedContent(String sheetName, int index) {
        // 获取合并的数量
        int mergedCount = getMergedCount(sheetName);
        // 校验
        if (mergedCount < index) {
            return null;
        }
        // 获取起始行
        int row = getFirstRowOfMerged(sheetName, index);
        // 起始列
        int column = getFirstColumnOfMerged(sheetName, index);

        return getAsString(sheetName, row, column);
    }

    public String getMergedContent(int sheetIndex, int index) {
        // 获取合并的数量
        int mergedCount = getMergedCount(sheetIndex);
        // 校验
        if (mergedCount < index) {
            return null;
        }
        // 获取起始行
        int row = getFirstRowOfMerged(sheetIndex, index);
        // 起始列
        int column = getFirstColumnOfMerged(sheetIndex, index);

        return getAsString(sheetIndex, row, column);
    }

    public int getMergedCount(String sheetName) {
        return workbook.getSheet(sheetName).getNumMergedRegions();
    }

    public int getMergedCount(int indexSeet) {
        return workbook.getSheetAt(indexSeet).getNumMergedRegions();
    }

    public Map<String, String> getMergerRowAndContent(String sheetName) {
        Map<String, String> map = new HashMap<String, String>();
        Sheet sheet = workbook.getSheet(sheetName);
        int sheetmergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetmergerCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            // 得到合并单元格的起始行, 结束行, 起始列, 结束列
            int firstC = ca.getFirstColumn();
            int firstR = ca.getFirstRow();
            int lastR = ca.getLastRow();
            String value = this.getAsString(sheetName, firstR, firstC);
            for (int row = ca.getFirstRow() + 1; row <= lastR; row++) {
                map.put(row + "," + firstC, value);
            }
        }
        return map;
    }

    /**
     * <p>
     * 获取有效值的行
     * </p>
     * 
     * @date 2012-10-11 上午10:45:20
     * @author MayuanChao
     * @param sheetIndex
     * @return
     * @return int
     */
    public int getValidValueFirstRow(int sheetIndex) {
        return 0;
    }

    public boolean createExcel() {
        boolean rtn = true;
        try {
            workbook = new HSSFWorkbook();
        } catch (Exception e) {
            log.error("POIExcelHelper.createExcel create Excel error", e);
            rtn = false;
        }
        return rtn;
    }

    public boolean createSheet(String sheetName) {
        boolean rtn = true;
        try {
            sheet = workbook.getSheet(sheetName);
            if (sheet != null) {
                for (int i = 0; i < Integer.MAX_VALUE; i++) {
                    sheet = workbook.getSheet(sheetName + "(" + i + ")");
                    if (sheet == null) {
                        sheet = workbook.createSheet(sheetName + "(" + i + ")");
                        break;
                    }
                }
            } else {
                sheet = workbook.createSheet(sheetName);
            }
            sheet.setDefaultColumnWidth(25);
            sheet.setDefaultRowHeight((short) 400);
            // sheet.setDisplayGridlines(true);
        } catch (Exception e) {
            rtn = false;
            log.error("POIExcelHelper.createSheet create sheet error", e);
        }
        return rtn;
    }

    public Row getRow(int row) {
        Row cellRow = sheet.getRow(row);
        if (cellRow == null) {
            cellRow = sheet.createRow(row);
        }
        cellRow.setHeight((short) 500);
        return cellRow;
    }

    public void setCellString(int col, int row, String contents) {
        try {
            Row cellRow = getRow(row);
            Cell cell = cellRow.createCell(col);
            cell.setCellValue(contents);
        } catch (Exception e) {
            log.error("POIExcelHelper.setCellString set cell value error", e);
        }
    }

    public void setCellString(int col, int row, String contents, int format) {
        try {
            Row cellRow = getRow(row);
            Cell cell = cellRow.createCell(col);
            cell.setCellValue(contents);
            setCellFont(cell, format);
        } catch (Exception e) {
            log.error("POIExcelHelper.setCellString set cell value error", e);
        }
    }

    public void setCellString(int col, int row, String contents, int format, String comment,
        String author) {
        try {
            Row cellRow = getRow(row);
            Cell cell = cellRow.createCell(col);
            cell.setCellValue(contents);
            setCellFont(cell, format);
            setCellComments(row, cell, comment, author);
        } catch (Exception e) {
            log.error("POIExcelHelper.setCellString set cell value error", e);
        }
    }

    public void setCellString(int col, int row, String contents, int format, String comment) {
        try {
            Row cellRow = getRow(row);
            Cell cell = cellRow.createCell(col);
            cell.setCellValue(contents);
            setCellFont(cell, format);
            setCellComments(row, cell, comment);
        } catch (Exception e) {
            log.error("POIExcelHelper.setCellString set cell value error", e);
        }
    }

    public void setCellDate(int col, int row, Date date) {
        try {
            Row cellRow = getRow(row);
            Cell cell = cellRow.createCell(col);
            cell.setCellValue(date);
        } catch (Exception e) {
            log.error("POIExcelHelper.setCellDate set cell value error", e);
        }
    }

    public void setCellDate(int col, int row, Calendar date) {
        try {
            Row cellRow = getRow(row);
            Cell cell = cellRow.createCell(col);
            cell.setCellValue(date);
        } catch (Exception e) {
            log.error("POIExcelHelper.setCellDate set cell value error", e);
        }
    }

    public void setCellNumber(int col, int row, double number) {
        try {
            Row cellRow = getRow(row);
            Cell cell = cellRow.createCell(col);
            cell.setCellValue(number);
        } catch (Exception e) {
            log.error("POIExcelHelper.setCellDate set cell value error", e);
        }
    }

    public void setCellNumber(int col, int row, int number) {
        try {
            Row cellRow = getRow(row);
            Cell cell = cellRow.createCell(col);
            cell.setCellValue(number);
        } catch (Exception e) {
            log.error("POIExcelHelper.setCellNumber set cell value error", e);
        }
    }

    public void setCellNumber(int col, int row, long number) {
        try {
            Row cellRow = getRow(row);
            Cell cell = cellRow.createCell(col);
            cell.setCellValue(number);
        } catch (Exception e) {
            log.error("POIExcelHelper.setCellNumber set cell value error", e);
        }
    }

    public void setCellNumber(int col, int row, float number) {
        try {
            Row cellRow = getRow(row);
            Cell cell = cellRow.createCell(col);
            cell.setCellValue(number);
        } catch (Exception e) {
            log.error("POIExcelHelper.setCellNumber set cell value error", e);
        }
    }

    public void setCellBoolean(int col, int row, boolean number) {
        try {
            Row cellRow = getRow(row);
            Cell cell = cellRow.createCell(col);
            cell.setCellValue(number);
        } catch (Exception e) {
            log.error("POIExcelHelper.setCellBoolean set cell value error", e);
        }
    }

    public void mergedCell(int firstRow, int lastRow, int firstCol, int lastCol) {
        try {
            CellRangeAddress cellRange = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            sheet.addMergedRegion(cellRange);
        } catch (Exception e) {
            log.error("POIExcelHelper.mergeCell Merged Cell error", e);
        }
    }


    public void setCellFont(Cell cell, int format) {
        CellStyle cs = null;
        switch (format) {
            case 0:
                cs = setBorderCellStyle(HSSFColor.SKY_BLUE.index, HSSFColor.SKY_BLUE.index);
                cell.setCellStyle(cs);
                break;
            case 1:
                cs = createCellStyle();
                cell.setCellStyle(cs);
                break;
            default:
                break;
        }
    }

    public CellStyle createCellStyle() {
        CellStyle style = workbook.createCellStyle();
        style.setWrapText(true);
        setCellFont(style, false, (short) 10);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        return style;
    }


    public void setRegionStyle(int firstRow, int lastRow, int firstCol, int lastCol) {
        for (int r = firstRow; r <= lastRow; r++) {
            Row row = sheet.getRow(r);
            if (row != null) {
                for (int c = firstCol; c <= lastCol; c++) {
                    Cell cell = row.getCell(c);
                    if (cell == null) {
                        cell = row.createCell(c);
                    }
                    cell.getCellStyle().setBorderLeft(HSSFCellStyle.BORDER_THIN);
                    cell.getCellStyle().setBorderRight(HSSFCellStyle.BORDER_THIN);
                    cell.getCellStyle().setBorderTop(HSSFCellStyle.BORDER_THIN);
                    cell.getCellStyle().setBorderBottom(HSSFCellStyle.BORDER_THIN);
                }
            }
        }
    }

    public CellStyle setBorderCellStyle(short backgroundColor, short foregroundColor) {
        CellStyle cs = workbook.createCellStyle();
        cs.setFillBackgroundColor(backgroundColor);
        // cs.setFillForegroundColor(foregroundColor);
        cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
        cs.setWrapText(true);
        setCellFont(cs, true, (short) 10);
        cs.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cs.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
        setBorder(cs);
        return cs;
    }

    public void setBorder(CellStyle style) {
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    }

    public void setCellFont(CellStyle style, boolean isbold, short fontSize) {
        Font font = workbook.createFont();
        font.setFontHeightInPoints(fontSize);// 设置字体大小
        if (isbold) {
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置为粗体
        }
        font.setFontName("Tahoma");
        style.setFont(font);
    }

    public void setAutoCellWidth(int col, int width) {
        sheet.setColumnWidth(col, width);
    }

    public boolean saveExcel(OutputStream out) {
        boolean rtn = true;
        try {
            workbook.write(out);
            if (out != null) {
                out.close();
            }
        } catch (Exception e) {
            rtn = false;
            log.error("POIExcelHelper.saveExcel saved excel error", e);
        }
        return rtn;
    }

    public Sheet getCurrentSheet() {
        return sheet;
    }

    /**
     * <p>
     * 添加下拉框的验证
     * </p>
     * 
     * @date 2012-10-18 上午10:38:59
     * @author MayuanChao
     * @param dataList
     * @param row
     * @param col
     */
    public void addValidation(String[] dataList, int row, int col) {
        DataValidation validation = null;
        String sheetname = sheet.getSheetName();
        String dropSheetName = sheetname + "_DropDatas" + col;
        if (dataList != null && dataList.length > 10) {// 采用引用的方式建立下拉
            Sheet dropListSheet = workbook.getSheet(dropSheetName);
            if (dropListSheet == null) {
                dropListSheet = workbook.createSheet(dropSheetName);
                int i = 0;
                for (String item : dataList) {
                    Row droprow = dropListSheet.createRow(i);
                    Cell dropcell = droprow.createCell(0);
                    dropcell.setCellValue(item);

                    i++;
                }
            }

            String dropDataListname = sheetname + "_dropDataList" + col;
            Name nameCell = workbook.getName(dropDataListname);
            if (nameCell == null) {
                nameCell = workbook.createName();
                nameCell.setNameName(dropDataListname);
                nameCell.setRefersToFormula(dropSheetName + "!$A$1:$A$" + dataList.length);

                DVConstraint constraint =
                    DVConstraint.createFormulaListConstraint(dropDataListname);
                CellRangeAddressList addressList = new CellRangeAddressList(row, MAX_ROW, col, col);

                DataValidationHelper dvHelper = dropListSheet.getDataValidationHelper();
                validation = dvHelper.createValidation(constraint, addressList);
            }


        } else {
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(dataList);
            CellRangeAddressList addressList = new CellRangeAddressList(row, MAX_ROW, col, col);
            validation = dvHelper.createValidation(dvConstraint, addressList);
        }

        // Note the check on the actual type of the DataValidation object.
        // If it is an instance of the XSSFDataValidation class then the
        // boolean value 'false' must be passed to the setSuppressDropDownArrow()
        // method and an explicit call made to the setShowErrorBox() method.
        if (validation != null) {
            if (validation instanceof XSSFDataValidation) {
                validation.setSuppressDropDownArrow(true);
                validation.setShowErrorBox(false);
            } else {
                validation.setSuppressDropDownArrow(false);
            }
            validation.setShowPromptBox(false);
            validation.createErrorBox("无效输入!", "请选择下拉列表");
            validation.createPromptBox("输入提示!", "请选择下拉列表里的内容!");

            int hideIndex = workbook.getSheetIndex(dropSheetName);
            if (hideIndex > -1) {
                workbook.setSheetHidden(hideIndex, true);
            }


            sheet.addValidationData(validation);
        }
    }

    public void setCellComments(int row, Cell cell, String commStr, String author) {
        CreationHelper factory = workbook.getCreationHelper();
        Drawing drawing = sheet.createDrawingPatriarch();
        // When the comment box is visible, have it show in a 1x3 space
        ClientAnchor anchor = factory.createClientAnchor();
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex() + 1);
        anchor.setRow1(row);
        anchor.setRow2(row + 3);

        // Create the comment and set the text+author
        Comment comment = drawing.createCellComment(anchor);
        RichTextString str = factory.createRichTextString(commStr);
        comment.setString(str);
        comment.setAuthor(author);
        // Assign the comment to the cell
        cell.setCellComment(comment);
    }

    public void setCellComments(int row, Cell cell, String commStr) {
        CreationHelper factory = workbook.getCreationHelper();
        Drawing drawing = sheet.createDrawingPatriarch();
        // When the comment box is visible, have it show in a 1x3 space
        ClientAnchor anchor = factory.createClientAnchor();
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex() + 1);
        anchor.setRow1(row);
        anchor.setRow2(row + 3);

        // Create the comment and set the text+author
        Comment comment = drawing.createCellComment(anchor);
        RichTextString str = factory.createRichTextString(commStr);
        comment.setString(str);
        // Assign the comment to the cell
        cell.setCellComment(comment);
    }

    public void validationDate(int row, int col) {
        DVConstraint dvconstraint = DVConstraint.createDateConstraint(
            DVConstraint.ValidationType.DATE, "1949-10-01", "9999-10-01", DATEFORMAT);
        CellRangeAddressList regions = new CellRangeAddressList(row, MAX_ROW, col, col);
        // 有效性--区域 组合
        DataValidation validation = new HSSFDataValidation(regions, dvconstraint);
        validation.setShowPromptBox(false);
        validation.createErrorBox("非法日期格式!", "请输入日期格式[" + DATEFORMAT + "]");
        validation.createPromptBox("输入提示!", "日期格式[" + DATEFORMAT + "]");
        sheet.addValidationData(validation);
    }

    public void validationNumeric(int row, int col) {
        DVConstraint dvconstraint = DVConstraint.createNumericConstraint(
            DVConstraint.ValidationType.DECIMAL, 3, Long.MIN_VALUE + "", Long.MAX_VALUE + "");
        CellRangeAddressList regions = new CellRangeAddressList(row, MAX_ROW, col, col);
        // 有效性--区域 组合
        DataValidation validation = new HSSFDataValidation(regions, dvconstraint);
        validation.setShowPromptBox(false);
        validation.createErrorBox("非法数字格式!", "请输入数字类型");
        validation.createPromptBox("输入提示!", "请输入数字类型");
        sheet.addValidationData(validation);
    }

    public void validationEmail(int row, int col) {
        String colLable = getExcelColumnLabel(col);
        String formula =
            "OR(COUNTIF(" + colLable + (row + 1) + ":" + colLable + "65500,\"*.png\")=1,COUNTIF("
                + colLable + (row + 1) + ":" + colLable + "65500,\"*.jpg\")=1)";
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        DataValidationConstraint dvconstraint = dvHelper.createCustomConstraint(formula);
        CellRangeAddressList regions = new CellRangeAddressList(row, MAX_ROW, col, col);
        DataValidation validation = dvHelper.createValidation(dvconstraint, regions);

        // DVConstraint dvconstraint = DVConstraint.createCustomFormulaConstraint(formula);
        // CellRangeAddressList regions = new CellRangeAddressList(row, MAX_ROW, col, col);
        // 有效性--区域 组合
        // DataValidation validation = new HSSFDataValidation(regions, dvconstraint);
        validation.setShowPromptBox(false);
        validation.createErrorBox("非法邮箱地址!", "请输入邮箱");
        validation.createPromptBox("输入提示!", "请输入邮箱");
        sheet.addValidationData(validation);


    }

    private String getExcelColumnLabel(int col) {
        String temp = "";
        // make sure how many letters are there
        double i = Math.floor(Math.log(25.0 * (col) / 26.0 + 1) / Math.log(26)) + 1;
        if (i > 1) {
            double sub = col - 26 * (Math.pow(26, i - 1) - 1) / 25;
            for (double j = i; j > 0; j--) {
                temp = temp + (char) (sub / Math.pow(26, j - 1) + 65);
                sub = sub % Math.pow(26, j - 1);
            }
        } else {
            temp = temp + (char) (col + 65);
        }
        return temp;
    }

    public static void main(String[] args) {
        POIExcelHelper excel = new POIExcelHelper();
        File excelFile = new File("D:/temp/1350612711140.xls");
        boolean rtn = excel.openExcel(excelFile);
        if (rtn) {
            System.out.println(excel.getAsString(0, 0, 1));
            System.out.println(excel.getAsString(0, 1, 1));
            System.out.println(excel.getAsString(0, 2, 1));

            System.out.println("*********************************8");

            System.out.println(excel.rowCount(0));
            excel.close();
        }

    }

}
